﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using HumanResourceManagementSystem.BusinessLogicLayer;

namespace HumanResourceManagementSystem.DataAccessLayer
{
    class SQL
    {
         
        //Cac phuong thuc doc ghi CSDL
        public static string ConnectionString = Settings.HRMSConnectString;
        public static List<UserEntity> getUserList()
        {
            List<UserEntity> userList = new List<UserEntity>();
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM User", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            foreach (DataRow dr in dt.Rows)
            {
                UserEntity ue = new UserEntity();
                ue.Username = dr["Username"].ToString();
                ue.Password = dr["Password"].ToString();
                userList.Add(ue);
            }
            return userList;
        }
        public static DataTable getEmployeeByName(string name)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("SELECT e.EmpID,EmpName,ProjName,Goal FROM Employee e LEFT JOIN Project p ON e.ProjID=p.ProjID WHERE EmpName like '%" + name + "%'", conn);
            da.Fill(dt);
            return dt;
        }

        public static DataTable getEmployeeByID(int empID)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("SELECT EmpID,EmpName,ProjName,Goal FROM Employee e LEFT JOIN Project p ON e.ProjID=p.ProjID WHERE EmpID=" + empID, conn);
            da.Fill(dt);
            return dt;
        }

        public static DataTable getProjects()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("SELECT ProjID,ProjName FROM Project ORDER BY ProjName", conn);
            da.Fill(dt);
            return dt;
        }

        public static DataTable getResources()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("SELECT ResID,ResName FROM Resource ORDER BY ResName", conn);
            da.Fill(dt);
            return dt;
        }
        
        public static DataTable getClients()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter("SELECT ClientID,ClientName FROM Client ORDER BY ClientName", conn);
            da.Fill(dt);
            return dt;
        }

        public static bool empAssign(int empID, int projID, string goal)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand comm = new SqlCommand("UPDATE Employee SET ProjID=@projID,Goal=@goal WHERE EmpID=@empID", conn);
            comm.Parameters.Add("@empID", SqlDbType.Int).Value = empID;
            comm.Parameters.Add("@projID", SqlDbType.Int).Value = projID;
            comm.Parameters.Add("@goal", SqlDbType.NVarChar).Value = goal;
            if (Convert.ToInt32(comm.ExecuteNonQuery()) != 0) result = true;
            else result = false;
            conn.Close();
            return result;
        }

        public static bool empoyeeIsAssigned(int empID)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand comm = new SqlCommand("SELECT EmpID FROM Employee WHERE EmpID=" + empID, conn);
            if (Convert.ToInt32(comm.ExecuteScalar()) != 0) result = true;
            else result = false;
            conn.Close();
            return result;
        }

        public static bool empoyeeExisted(int empID)
        {
            bool bresult = false;
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand comm = new SqlCommand("SELECT EmpID FROM Employee WHERE EmpID=" + empID, conn);
            if (Convert.ToInt32(comm.ExecuteScalar()) != 0) bresult = true;
            else bresult = false;
            conn.Close();
            return bresult;
        }

        public static bool resAssign(int resID, int empID, DateTime allocDate, DateTime freeDate)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand comm = new SqlCommand("UPDATE Resource SET EmpID=@empID,AllocDate=@allocDate,FreeDate=@freeDate WHERE ResID=@resID", conn);
            comm.Parameters.Add("@resID", SqlDbType.Int).Value = resID;
            comm.Parameters.Add("@empID", SqlDbType.Int).Value = empID;
            comm.Parameters.Add("@allocDate", SqlDbType.Date).Value = allocDate.ToShortDateString();
            comm.Parameters.Add("@freeDate", SqlDbType.Date).Value = freeDate.ToShortDateString();
            if (Convert.ToInt32(comm.ExecuteNonQuery()) != 0) result = true;
            else result = false;
            conn.Close();
            return result;
        }
    }
}
